<?php
/**
 * Oracle访问类
 * @author Terry
 * @copyright baoling@yeah.net
 * 
 */
Class DB_Oracle{
    /**
     * 数据库请求次数
     *
     * @var Int
     */
    var $query_num = 0;
    /**
     * 数据库连接信息
     *
     * @var Array
     */
    var $dbinfo=null;
    /**
     * 数据库连接句柄
     *
     * @var resource
     */
    /**
     * 是否强制转换字段名为小写
     *
     * @var boolean
     */
    var $fieldNameLower = false;
    
    var $conn = null;
    
    var $_lastrs = null;
    /**
     * 最后一次数据库操作的错误信息
     *
     * @var mixed
     */
    var $lasterr = null;
    /**
     * 最后一次数据库操作的错误代码
     *
     * @var mixed
     */
    var $lasterrcode=null;

     /**
     * 指示事务是否启用了事务
     *
     * @var int
     */
    var $_transflag = false;
    /**
     * 启用事务处理情况下的错误
     *
     * @var Array
     */
    var $_transErrors = array();
            
    function DB_Oracle($dbinfo){

        if(is_array($dbinfo)){
            $this->dbinfo=$dbinfo;
        }else{
            showError(__T('Miss argument!Please check file conf.php!'));
        }
    }

    /**
     * 数据库连接
     *
     * @param Array $dbinfo
     * @return boolean
     */

     function connect($dbinfo = false)
    {

        if ($this->conn && $dbinfo == false) { return true; }
        
        if (!$dbinfo) {
            $dbinfo = $this->dbinfo;
        } else {
            $this->dbinfo = $dbinfo;
        }
        if (isset($dbinfo['charset']) && $dbinfo['charset'] != '') {
            $charset = $dbinfo['charset'];
        }
        
        if (strtoupper($charset) == 'GB2312') { $charset = 'GBK'; }
        
        
        $this->conn = ocilogon($dbinfo['dbuser'],$dbinfo['dbpass'],$dbinfo['dbname']);

        if (!$this->conn) {
           showError(sprintf(__T('Connect to Oracle (%s) failed!'),$dbinfo['dbname']));
        }

        return true;
    }

    /**
     * 关闭数据库连接
     */
    function close()
    {
        if ($this->conn) {
            ocilogoff($this->conn);
        }
        $this->conn = null;
    }
    function q_field($tableName){
        if (substr($tableName, 0, 1) == '"') { return $tableName; }
        return '"' . $tableName . '"';
    }
    
    function q_str($value){
        if(!$this->conn){
            $this->connect();
        }
        
        if (is_bool($value)) { return $value ? 1:0; }
        if (is_null($value)) { return 'NULL'; }
        //return "'".$value."'";
        return  "'" . str_replace("'", "''", $value) . "'";
    }
    /**
     * 直接查询Sql
     *
     * @param String $SQL
     * @return Mix
     */
    function query($sql) {
        if(!$this->conn){
            $this->connect();
        }
        
        $stmt = @ociparse($this->conn, $sql);
         if ($stmt) {
             if (!@ociexecute($stmt)) { 
                $err = ocierror($stmt);
             }else{
                 $this->query_num++;
                  $this->_lastrs=$stmt;
                 return $stmt;
             }
         }
        else{
            $err = ocierror();
        }
        
        $this->lasterr = $err['message'];
        $this->lasterrcode = $err['code'];
        if($this->_transflag){
            $this->_transErrors[]['sql'] = $sql;
            $this->_transErrors[]['errcode'] = $this->lasterrcode;
            $this->_transErrors[]['err'] = $this->lasterr;
        }else{
            showError('ERROR:' . $sql .' ERROR_INFO:'.$this->lasterrcode.','.$this->lasterr);
        }
        return false;
    }
    
    function getAll($sql){
       if (is_resource($sql)) {
            $stmt = $sql;
        } else {
            $stmt = $this->query($sql);
        }
        $data = array();
        $row = false;
        if ($this->fieldNameLower) {
            while (ocifetchinto($stmt, $row, OCI_ASSOC)) {
                $data[] = array_change_key_case($row, CASE_LOWER);
            }
        } else {
            while (ocifetchinto($stmt, $row, OCI_ASSOC)) {
                $data[] = $row;
            }
        }
        ocifreestatement($stmt);
        return $data;
        
    }
    function getOne($sql)
    {
        if (is_resource($sql)) {
            $stmt = $sql;
        } else {
            $stmt = $this->query($sql);
        }
        $row = array();
        ocifetchinto($stmt, $row, OCI_NUM);
        ocifreestatement($stmt);
        return isset($row[0]) ? $row[0] : null;
    }

    /**
     * 执行查询，返回第一条记录
     *
     * @param string|resource $sql
     *
     * @return mixed
     */
    function getRow($sql)
    {
       if (is_resource($sql)) {
            $stmt = $sql;
        } else {
            $stmt = $this->query($sql);
        }
        $row = array();
        @ocifetchinto($stmt, $row, OCI_ASSOC);
        @ocifreestatement($stmt);
        if ($this->fieldNameLower) {
            return array_change_key_case($row, CASE_LOWER);
        } else {
            return $row;
        }
    }

    /**
     * 执行查询，返回结果集的指定列
     *
     * @param string|resource $sql
     * @param int $col 要返回的列，0 为第一列
     *
     * @return mixed
     */
    function getCol($sql, $col = 0)
    {
        if (is_resource($sql)) {
            $stmt = $sql;
        } else {
            $stmt = $this->query($sql);
        }
        $data = array();
        $row = array();
        while (@ocifetchinto($stmt, $row, OCI_NUM)) {
            $data[] = $row[$col];
        }
        @ocifreestatement($stmt);
        return $data;
    }
    
    function getAssoc($sql){
        if (is_resource($sql)) {
            $stmt = $sql;
        } else {
            $stmt = $this->query($sql);
        }
        $data = array();
        while (@ocifetchinto($stmt, $row, OCI_NUM)) {
            $data[$row[0]] = $row[1];
        }
        $row = array();
        @ocifreestatement($stmt);
        return $data;
    }
    /**
     * 加入边界的查询语句
     *
     * @param String $sql
     * @param Int $length
     * @param Int $offset
     * @return Resource
     */
  function selectLimit($sql, $count , $offset = 0)
    {
         $count = intval($count);
        if ($count <= 0) {
            showError(sprintf(__T("LIMIT argument count=%s invalid!"),$count));
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /**
             * @see Zend_Db_Adapter_Oracle_Exception
             */
            showError(sprintf(__T("LIMIT argument offset=%s invalid!"),$offset));
        }

        /**
         * Oracle does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*", 
         * this puts an extra column into the query result set.
         */
        $limit_sql = "SELECT z2.*
            FROM (
                SELECT ROWNUM AS FEX_rownum, z1.*
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.FEX_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql;
    }
    /**
     * 返回数组
     *
     * @param resouce $query
     * @return Array
     */
    function fetchArray($stmt) {
        $row = array();
        @ocifetchinto($stmt, $row, OCI_ASSOC);
        if ($this->fieldNameLower) {
            return array_change_key_case($row, CASE_LOWER);
        } else {
            return $row;
        }
    }
    /**
     * 返回最近一次数据库操作受到影响的记录数
     *
     * @return int
     */
    function affectedRows() {
         if (is_resource($this->_lastrs)) { return ocirowcount($this->_lastrs); }
        return 0;
    }
    /**
     * 从记录集中返回一行数据
     *
     * @param resouce $query
     *
     * @return array
     */
    function fetchRow($query) {
        $row=array();
        @ocifetchinto($stmt, $row, OCI_ASSOC);
        return $row;
        
    }

    /**
     * Enter description here...
     *
     * @param string $sql
     * @return Int
     */
    function numRows($sql) {
        return $this->getOne('select count(*) from ('.$sql.')');
    }
    /**
     * 获取当前Oracle的版本号(暂无此功能)
     *
     * @return String
     */
    function version() {
        return 0;
    }
    /**
     * 获得刚插入数据的ID号(暂无此功能)
     *
     * @return Int
     */
    function insertId() {
        return 0;
    }

    /**
     * 获得查询数据库的次数
     *
     * @return Int
     */
    function getQueryNum(){
        return $this->query_num;
    }

    /**
     * 返回数据库可以接受的日期格式
     *
     * @param int $timestamp
     */
    function dbTimeStamp($timestamp)
    {
        if (empty($timestamp) && $timestamp !== 0) { return 'null'; }
        return 'TO_DATE(' . date('Y-m-d, h:i:s A', $timestamp) . ", 'RRRR-MM-DD, HH:MI:SS AM')";
    }
    function strRandom(){
        return null;
    }
    /**
     * 启动事务
     */
    function startTrans()
    {
        $this->_transflag = true;
        $this->_transErrors = array();
        return true;
    }

    /**
     * 提交事务
     *
     */
    function commit()
    {
        $this->_transflag = false;
        ocicommit($this->conn);
        return true;
    }
    /**
     * 回滚事务
     *
     */
    function rollback(){
        $this->_transflag = false;
        ocirollback($this->conn);
        return true;
    }
    
    function transErrors(){
        $errors = $this->_transErrors;
        if(is_array($errors)){
            foreach($errors as $error){
                echo 'SQL:' . $error['sql'] .' ERROR_INFO:'.$error['errcode'].','.$error['err'];
            }
        }
        die();
    }

}